/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package core;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import javax.swing.JFrame;
import javax.swing.JTable;
import util.JTableUtil;

/**
 *
 * @author Kuchcik
 */
public class Users {
    
    private JFrame parent;
    
    public Users(JFrame parent){
    this.parent=parent;
    }
    
    public boolean updateUser(String FirstName, String LastName, Status status, String eMail, int isActive){
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("update users set imie = '"+FirstName+"', nazwisko='"+
            LastName+"', id_uprawnienia="+ (status.ordinal()+1)+", email='"+eMail+
                    "', is_active="+isActive+" where email='"+eMail+"'");
            return true;
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd danych", true,parent);
        }
        return false;
    }
    
    public boolean deleteUser(String eMail){
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("delete from users where email='"+eMail+"'");
            return true;
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd danych", true,parent);
        }
        return false;        
    }
    
    public String[] getDoctorsList(){
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("Select id,imie, nazwisko from users where id_uprawnienia = 2");
            ResultSet rs = stmn.getResultSet();
            
            int n=SqlConnection.getRowCount(rs);
            String[] data = new String[n];
            rs.next();
            for(int i=0;i<n;i++){
                data[i]=rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3);
                rs.next();
            }
            return data;
            
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd pobierania listy medyków", true,parent);
        }
        
        return null;
    }
    
        public static String[] getDoctorsList_COMBOBOX(){
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT U.id, S.nazwa, U.imie, U.nazwisko FROM users U JOIN medycy M ON U.id = M.id_user JOIN medycy_specjalizacje S ON M.id_specjalizacji = S.id ORDER BY S.nazwa ASC");
            ResultSet rs = stmn.getResultSet();
            
            int n=SqlConnection.getRowCount(rs);
            String[] data = new String[n];
            rs.next();
            for(int i=0;i<n;i++){
                data[i]=rs.getString(1)+": "+rs.getString(2)+" - "+rs.getString(3)+" "+rs.getString(4);
                rs.next();
            }
            return data;
            
        } catch (SQLException ex ) {
            System.out.println("getDoctorsList_COMBO - jakis blad z baza");
            //ErrorLogger.getInstance().log("Błąd pobierania listy medyków", true,parent);
        }
        
        return null;
    }

    public String[] getDoctorsSpecList(){
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("Select nazwa from medycy_specjalizacje");
            ResultSet rs = stmn.getResultSet();
            
            int n=SqlConnection.getRowCount(rs);
            String[] data = new String[n];
            rs.next();
            for(int i=0;i<n;i++){
                data[i]=rs.getString(1);
                rs.next();
            }
            return data;
            
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd pobierania listy specjalizacji", true,parent);
        }
        
        return null;
    }    
    
    public JTable getAllUsers() throws Exception{
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT U.imie AS Imię,U.nazwisko AS Nazwisko,UP.nazwa AS Uprawnienie,U.email AS eMail,U.is_active AS czyAktywny FROM users as U join uprawnienia as UP"
                    + " on U.id_uprawnienia=UP.id");
            ResultSet rs = stmn.getResultSet();
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
    public boolean addUser(User user, String password, int isActive){
                try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            stmn.execute("insert into users values("+user.getAuthorizationID()+",'"+user.getLogin()+"','"+
                    password+"','"+user.getFirstName()+"','"+user.getLastName()+"','"+user.getEMail()+"',"+
                    Integer.toString(isActive)+");");
            return true;                 
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log(ex.getMessage(), true,parent);
        }
        
        return false;
    }
    
    public static User getDoctorByHisID(int idpa, JFrame curr) {
       try { 
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT id, id_uprawnienia, login, imie, nazwisko, email, is_active FROM users  WHERE id='"+idpa+"'");
            ResultSet rs = stmn.getResultSet();
            rs.first();
            User zwrot = new User(Integer.parseInt(rs.getString(1)), Status.Medyk, rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), Boolean.parseBoolean(rs.getString(7)), curr);
            return zwrot; 
        } catch (SQLException ex ) {
           System.out.println("Users getDoctorByHisID- blad polaczenia");
            // ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }

    public static User getDoctorByPacient(int idpa, JFrame curr) {
       try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT U.id, U.id_uprawnienia, U.login, U.imie, U.nazwisko, U.email, U.is_active FROM pacjenci P JOIN users U ON P.id_medyka = U.id WHERE P.id_user='"+idpa+"'");
            ResultSet rs = stmn.getResultSet();
            rs.first();
            User zwrot = new User(Integer.parseInt(rs.getString(1)), Status.Medyk, rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), Boolean.parseBoolean(rs.getString(7)), curr);
            
            return zwrot; 
        } catch (SQLException ex ) {
           System.out.println("Users getDoctorByPacient - blad polaczenia");
            // ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return null;
    }
    
     public static int getDoctorByPacient_ID(int idpa) {
       try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute("SELECT U.id FROM pacjenci P JOIN users U ON P.id_medyka = U.id WHERE P.id_user='"+idpa+"'");
            ResultSet rs = stmn.getResultSet();
            rs.first();    
            return Integer.parseInt(rs.getString(1)); 
        } catch (SQLException ex ) {
           System.out.println("Users getDoctorByPacient_ID - blad polaczenia");
            // ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true,parent);
        }
    return 0;
    }
        
    public static boolean addWizytyDomoweTermin(User me, Doctor doctor, Date from, Date to) {
        try {
        if(from.compareTo(to) > 0) {
           Date temp = to;
           to = from;
           from = to;
        }
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

            stmn.execute("");
            return true;                 
        } catch (SQLException ex ) {
           // ErrorLogger.getInstance().log(ex.getMessage(), true,parent);
        }
        
        return false;
    }
    public JTable getUsers(int id, Status status,String login, String FirstName, String LastName, String eMail, String isActive){
        
        if(status==null && login==null && FirstName==null && LastName==null && eMail==null && isActive==null){
            ErrorLogger.getInstance().log("Nie wprowadzono danych", true, parent);
            return null;
        }//sprawdzenie poprawnosci
        
        String query="SELECT U.imie AS Imię, U.nazwisko AS Nazwisko, UP.nazwa AS Uprawnienie, U.email AS eMail, U.is_active AS czyAktywny FROM"
                + " users AS U join uprawnienia AS UP on U.id_uprawnienia=UP.id WHERE ";
        boolean isfirstWhere=true;//flaga do AND'ów
        
        if(status!=null){
            query+="id_uprawnienia="+status.ordinal();
            isfirstWhere=false;
        }
        if(isfirstWhere==false && id!=-1) query+=" AND ";
        
        if(id!=-1){
            query+="id="+id;
            isfirstWhere=false;
        }       
        
        if(isfirstWhere==false && login!=null) query+=" AND ";
        
        if(login!=null){
            query+="login="+login;
            isfirstWhere=false;
        }
       
        if(isfirstWhere==false && FirstName!=null) query+=" AND ";
        
        if(FirstName!=null){
            query+="imie='"+FirstName+"'";
           isfirstWhere=false;
        }
        
        if(isfirstWhere==false && LastName!=null) query+=" AND ";
       
        if(LastName!=null){
            query+="nazwisko='"+LastName+"'";
            isfirstWhere=false;
        }
        if(isfirstWhere==false && eMail!=null) query+=" AND ";
        
        if(eMail!=null){
            query+="email='"+eMail+"'";
            isfirstWhere=false;
        }
        if(isfirstWhere==false && isActive!=null) query+=" AND ";
        
        if(isActive!=null) query+="is_active="+isActive+"";
        query+=";";
        //koniec zapytania        
        
        try {
            Statement stmn = SqlConnection.getInstance().getSqlConnection().createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmn.execute(query);
            ResultSet rs = stmn.getResultSet();
            
            return JTableUtil.createTable(rs);
        } catch (SQLException ex ) {
            ErrorLogger.getInstance().log("Błąd połączenia z bazą danych", true, parent);
        }              
        
        return null;
        
    }
    
}
